﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DataHelper;

namespace DAL
{
   public static class TableInfo
    {

        /// <summary>
        /// 获取表的结构
        /// 包含4列  列名，数据类型，长度，是否主键
        /// </summary>
        /// <param name="tableName">表的名字</param>
        /// <returns>4列的DataTable</returns>
       public static DataTable GetTableStruct( string tableName)
        {
            return SQLHelper.ExecuteSqlGetDataSet(GetColsSQL(tableName)).Tables[0];
            
        }
       /// <summary>
       /// 组织SQL语句
       /// </summary>
       /// <param name="tableName">表的名字</param>
       /// <returns>列名,列类型，类型的长度，是否为主键，是否可以为空(0非空)</returns>
       public static string GetColsSQL(string tableName)
        {
            string strSQL = @"SELECT syscolumns.Name as col_name, SysTypes.Name as col_type, syscolumns.Length as col_len, isnull(sys.extended_properties.Value,syscolumns.Name) as col_memo,
case when syscolumns.name in 
(select primarykey=a.name
FROM syscolumns a
 inner join sysobjects b on a.id=b.id  and b.xtype='U' and b.name<>'dtproperties'
 where  exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
 SELECT name FROM sysindexes WHERE indid in(
  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid and status=2066
 ))) 
and b.name=sysobjects.Name 
)
 then 1 else 0 end as is_key,syscolumns.isnullable as col_null
 FROM sysobjects,SysTypes,syscolumns 
LEFT JOIN sys.extended_properties ON (Syscolumns.Id = sys.extended_properties.major_id AND
 Syscolumns.Colid = sys.extended_properties.minor_id)
";
            strSQL += " WHERE sysobjects.name='" + tableName + "' and (sysobjects.Xtype ='u' OR Sysobjects.Xtype ='v') ";

            strSQL += @" AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType 
AND SysTypes.Name <> 'sysname' AND Sysobjects.Name Like '%' ORDER By  SysColumns.colid ";

            return strSQL;
        }
    }
}
